package com.live.shop.sql;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MyDatabaseHelper extends SQLiteOpenHelper {
	//用户信息
	public static final String user_info = "create table user_info ("
			+ "id integer primary key autoincrement, "
			+ "uuid integer DEFAULT 0,"
			+ "nickname text DEFAULT '',"
			+ "account text DEFAULT '',"
			+ "password text DEFAULT '',"
			+ "endtime text DEFAULT '',"
			+ "headurl text DEFAULT '',"
			+ "status integer DEFAULT 1,"//用户状态 0其他设备登录 1正常
			+ "comment_keep integer DEFAULT 1,"//保留评论状态 0不保留 1保留
			+ "prologue_interval integer DEFAULT 3,"//直播话术间隔（秒）
			+ "prologue_break integer DEFAULT 1,"//允许话术被公屏打断 0不允许 1允许
			+ "prologue_restart integer DEFAULT 1,"//打断的话术是否从开头播放 0不是 1是
			+ "prologue_playtype integer DEFAULT 0,"//话术播放方式 0轮播 1随机
			+ "keyword_keeptype integer DEFAULT 0,"//关键词回复播放 0评论实时 1评论历史
			+ "pc_ip text DEFAULT '',"//电脑端IP
			+ "function_id integer DEFAULT 0,"//选择哪个应用平台
			+ "function_event_code int DEFAULT -1,"//事件类型
			+ "function_resid text DEFAULT '',"//资源ID
			+ "function_package text DEFAULT '',"//包名
			+ "token text DEFAULT '',"
			+ "aliyun_akid text DEFAULT '',"//阿里云声音 AccessKeyID
			+ "aliyun_aksecret text DEFAULT '',"//阿里云声音 AccessKeySecret
			+ "aliyun_appkey text DEFAULT '',"//阿里云声音AppKey
			+ "aliyun_voice_select text DEFAULT '',"//阿里云声音 音色选择后标题（场景+声音名称+类型）
			+ "aliyun_font_name text DEFAULT '',"//阿里云声音 英文声音参数
			+ "aliyun_speed_level text DEFAULT '',"//阿里云声音 语速
			+ "aliyun_volume text DEFAULT '',"//阿里云声音 音量
			+ "aliyun_token text DEFAULT '',"
			+ "aliyun_expired_time integer DEFAULT ''"
			+") ";
	//关键词列表
	public static final String keyword_list = "create table keyword_list ("
			+ "keyword_id integer primary key autoincrement, "
			+ "question text DEFAULT '', "//问题
			+ "keyword text DEFAULT '', "//关键词
			+ "use_num integer DEFAULT 0, "//使用次数
			+ "keyword_status integer DEFAULT 0, "//启用状态 0未启用 1启用
			+ "create_time text DEFAULT ''"//创建时间
			+")";
	//关键词回复详情
	public static final String keyword_record = "create table keyword_record ("
			+ "id integer primary key autoincrement, "
			+ "keyword_id integer DEFAULT 0, "//关键词ID
			+ "answer_txt text DEFAULT '', "//回答文本
			+ "answer_type integer DEFAULT 0, "//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
			+ "answer_state integer DEFAULT 0, "//合成状态 0.暂无 1.已录音 2.已合成 3.已选择
			+ "answer_audio text DEFAULT '', "//回答音频
			+ "use_num integer DEFAULT 0, "//使用次数
			+ "create_time text DEFAULT ''"//创建时间
			+")";
	//开场白
	public static final String prologue_list = "create table prologue_list ("
			+ "prologue_id integer primary key autoincrement, "
			+ "prologue_txt text DEFAULT '', "//开场白文本
			+ "prologue_type integer DEFAULT 0, "//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
			+ "prologue_state integer DEFAULT 0, "//合合成状态 0.暂无 1.已录音 2.已合成 3.已选择
			+ "prologue_audio text DEFAULT '', "//回答音频
			+ "prologue_status integer DEFAULT 0, "//启用状态 0未启用 1启用
			+ "use_num integer DEFAULT 0, "//使用次数
			+ "create_time text DEFAULT ''"//创建时间
			+") ";
	//背景音乐
	public static final String music_list = "create table music_list ("
			+ "music_id integer primary key autoincrement, "
			+ "music_name text DEFAULT '', "//音乐名称
			+ "music_url text DEFAULT '', "//音乐地址
			+ "play_num integer DEFAULT 0, "//播放次数
			+ "create_time text DEFAULT ''"//创建时间
			+") ";
	//直播互动
	public static final String comments_list = "create table comments_list ("
			+ "comments_id integer primary key autoincrement, "
			+ "comments_user text DEFAULT '', "//评论人
			+ "comments_txt text DEFAULT '', "//评论内容
			+ "comments_mark integer DEFAULT 0, "//是否标记 0.未标记 1已标记
			+ "comments_type integer DEFAULT 0, "//类型 1用户进直播间 2评论 3关注主播 4刷礼物
			+ "keyword_id integer DEFAULT 0, "//关键词ID
			+ "keyword_mate integer DEFAULT 0, "//匹配状态 0未匹配 1无匹配 2 问答关键词有匹配 3素材匹配
			+ "keyword_reply integer DEFAULT 0, "//回复状态 0未回复 1已回复
			+ "keyword_audio text DEFAULT '', "//回复音频路径
			+ "create_time text DEFAULT ''"//创建时间
			+") ";

	//WebSocket局域网IP设置
	public static final String ipconfig = "create table ipconfig ("
			+ "id integer primary key autoincrement, "
			+ "server_ip text DEFAULT '', "//服务端IP
			+ "server_port text DEFAULT ''"//服务端端口
			+") ";

	//产品组列表
	public static final String product_group_list = "create table product_group_list ("
			+ "product_group_id integer primary key autoincrement, "
			+ "product_group_name text DEFAULT '', "//产品组名称
			+ "product_group_status integer DEFAULT 0, "//启用状态 0未启用 1启用
			+ "create_time text DEFAULT ''"//创建时间
			+")";
	//产品组中的素材列表
	public static final String product_material_record = "create table product_material_record ("
			+ "id integer primary key autoincrement, "
			+ "product_group_id integer DEFAULT 0, "//产品组ID
			+ "material_name text DEFAULT '', "//素材名称
			+ "material_type integer DEFAULT 0, "//素材类型 0.图片 1.视频 2.文字
			+ "material_fileuri text DEFAULT '', "//素材文件Uri路径
			+ "material_filepath text DEFAULT '', "//素材文件真实路径
			+ "location_x integer DEFAULT 0, "//位置X坐标移动值
			+ "location_y integer DEFAULT 0, "//位置Y坐标移动值
			+ "scale_rate integer DEFAULT 100, "//缩放率 1-200
			+ "hide_state integer DEFAULT 0, "//隐藏状态 0 显示 1隐藏
			+ "material_show_state integer DEFAULT 0, "//素材展示时间状态[确保隐藏状态为 0，否则不生效]  0长时间（将按照设置的展示时间） 1自义定  2跟随关键词回复时间（必须设置关键词）
			+ "material_key_showtime integer DEFAULT 0, "//素材关键词触发后展示时间自定义（秒）
			+ "keyword text DEFAULT '', "//关键词
			+ "answer_txt text DEFAULT '', "//回答文本
			+ "answer_type integer DEFAULT 0, "//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
			+ "answer_state integer DEFAULT 0, "//合成状态 0.暂无 1.已录音 2.已合成 3.已选择
			+ "answer_audio text DEFAULT '', "//回答音频
			+ "create_time text DEFAULT ''"//创建时间
			+")";

	public MyDatabaseHelper(Context context, String name, CursorFactory cursorfactory, int version,
                            DatabaseErrorHandler databaseerrorHandler) {
		super(context, name, cursorfactory, version, databaseerrorHandler);

	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		//创建数据库时会调用，我们需要在里面创建我们的数据表。onCreate只会调用一次，如果数据库已经存在，那么打开数据库是不会回调onCreate的。
		db.execSQL(user_info);
		db.execSQL(keyword_list);
		db.execSQL(keyword_record);
		db.execSQL(prologue_list);
		db.execSQL(music_list);
		db.execSQL(comments_list);
		db.execSQL(ipconfig);
		db.execSQL(product_group_list);
		db.execSQL(product_material_record);
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//升级数据库时会调用，我们可以在这里面做升级操作
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
//		// onOpen在每次打开数据库时都会调用。
		if(IsHaveTable(db,"train_list")){
			db.execSQL("DROP TABLE IF EXISTS train_list;");//删除表
		}
		if(IsHaveTable(db,"tts_cache_list")){
			db.execSQL("DROP TABLE IF EXISTS tts_cache_list;");
		}
		if(!checkColumnExists2(db,"keyword_list","question")){
			db.execSQL("alter table keyword_list add column question text DEFAULT ''");
		}
		if(!checkColumnExists2(db,"prologue_list","prologue_status")){
			db.execSQL("alter table prologue_list add column prologue_status integer DEFAULT 0");
		}
		if(!checkColumnExists2(db,"user_info","uuid")){
			db.execSQL("alter table user_info add column uuid integer DEFAULT 0");
		}
		if(!checkColumnExists2(db,"user_info","endtime")){
			db.execSQL("alter table user_info add column endtime text DEFAULT ''");
		}
		if(!checkColumnExists2(db,"user_info","nickname")){
			db.execSQL("alter table user_info add column nickname text DEFAULT ''");
		}
		if(!checkColumnExists2(db,"user_info","prologue_playtype")){
			db.execSQL("alter table user_info add column prologue_playtype integer DEFAULT 0");
		}
		if(!checkColumnExists2(db,"user_info","keyword_keeptype")){
			db.execSQL("alter table user_info add column keyword_keeptype integer DEFAULT 0");
		}
		if(!checkColumnExists2(db,"comments_list","keyword_mate")){
			db.execSQL("alter table comments_list add column keyword_mate integer DEFAULT 0");
		}
		if(!checkColumnExists2(db,"comments_list","keyword_reply")){
			db.execSQL("alter table comments_list add column keyword_reply integer DEFAULT 0");
		}
		if(!checkColumnExists2(db,"comments_list","comments_type")){
			db.execSQL("alter table comments_list add column comments_type integer DEFAULT 0");
		}
		//新版本
		if(!IsHaveTable(db,"ipconfig")){
			db.execSQL(ipconfig);
		}
		if(!IsHaveTable(db,"product_group_list")){
			db.execSQL(product_group_list);
		}
		if(!IsHaveTable(db,"product_material_record")){
			db.execSQL(product_material_record);
		}
		if(!checkColumnExists2(db,"comments_list","keyword_audio")){
			db.execSQL("alter table comments_list add column keyword_audio text DEFAULT ''");
		}
	}
	//检查某表是否存在
	public static boolean IsHaveTable(SQLiteDatabase db,String tablename){
		boolean isHave = false;
		Cursor cursor = db.rawQuery("select name from sqlite_master where type='table' ", null);
		while (cursor.moveToNext()) {
			// 遍历出表名
			if (cursor.getString(0).equals(tablename)) {
				isHave = true;
				break;
			}
		}
		cursor.close();
		return isHave;
	}
	/**
	 * 检查表中某列是否存在
	 */
	private boolean checkColumnExists2(SQLiteDatabase db, String tableName
			, String columnName) {
		boolean result = false ;
		Cursor cursor = null ;
		try{
			cursor = db.rawQuery( "select * from sqlite_master where name = ? and sql like ?"
					, new String[]{tableName , "%" + columnName + "%"} );
			result = null != cursor && cursor.moveToFirst() ;
		}catch (Exception e){
			Log.e("TAG","checkColumnExists2..." + e.getMessage()) ;
		}finally{
			if(null != cursor && !cursor.isClosed()){
				cursor.close() ;
			}
		}
		return result ;
	}
}
